/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50716
 Source Host           : localhost
 Source Database       : pdm

 Target Server Type    : MySQL
 Target Server Version : 50716
 File Encoding         : utf-8

 Date: 02/04/2017 09:40:33 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `d_bluetooth_info`
-- ----------------------------
DROP TABLE IF EXISTS `d_bluetooth_info`;
CREATE TABLE `d_bluetooth_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '蓝牙扩展信息ID',
  `sn` varchar(20) NOT NULL COMMENT '设备SN',
  `mac_address_0` char(17) DEFAULT NULL COMMENT 'MAC地址1',
  `mac_address_1` char(17) DEFAULT NULL COMMENT 'MAC地址2',
  `encrypt_key` varchar(50) DEFAULT NULL COMMENT '秘钥',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sn` (`sn`),
  CONSTRAINT `d_bluetooth_info_ibfk_1` FOREIGN KEY (`sn`) REFERENCES `d_device` (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='蓝牙信息表';

-- ----------------------------
--  Table structure for `d_device`
-- ----------------------------
DROP TABLE IF EXISTS `d_device`;
CREATE TABLE `d_device` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '设备ID',
  `sn` varchar(20) NOT NULL COMMENT '设备序列号',
  `model_id` bigint(20) NOT NULL COMMENT '型号ID',
  `hardware_version` varchar(10) DEFAULT NULL COMMENT '硬件版本号',
  `software_version` varchar(10) DEFAULT NULL COMMENT '软件版本号',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '设备状态 0：初始 1：生产 2：已出货 3：退货',
  `firm_id` bigint(20) NOT NULL COMMENT '厂商ID',
  `generator_id` bigint(20) NOT NULL COMMENT '设备生成ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sn` (`sn`),
  KEY `model_id` (`model_id`),
  KEY `firm_id` (`firm_id`),
  KEY `generator_id` (`generator_id`),
  CONSTRAINT `d_device_ibfk_1` FOREIGN KEY (`model_id`) REFERENCES `d_model` (`model_id`),
  CONSTRAINT `d_device_ibfk_2` FOREIGN KEY (`firm_id`) REFERENCES `d_firm` (`firm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='设备表';

-- ----------------------------
--  Table structure for `d_distribution_record`
-- ----------------------------
DROP TABLE IF EXISTS `d_distribution_record`;
CREATE TABLE `d_distribution_record` (
  `generator_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '设备生成ID',
  `generator_num` varchar(30) NOT NULL COMMENT '生成编号',
  `model_id` bigint(20) NOT NULL COMMENT '设备类型ID',
  `quantity` int(11) NOT NULL COMMENT '生成数量',
  `firm_id` bigint(20) NOT NULL COMMENT '厂商ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者ID',
  PRIMARY KEY (`generator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='设备生成记录表';

-- ----------------------------
--  Table structure for `d_firm`
-- ----------------------------
DROP TABLE IF EXISTS `d_firm`;
CREATE TABLE `d_firm` (
  `firm_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL COMMENT '名称',
  `tel` varchar(100) DEFAULT NULL COMMENT '电话',
  `fax` varchar(100) DEFAULT NULL COMMENT '传真',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `address` varchar(500) DEFAULT NULL COMMENT '地址',
  `sync_url` varchar(500) DEFAULT NULL COMMENT '数据同步请求地址',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`firm_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='车厂信息表';

-- ----------------------------
--  Records of `d_firm`
-- ----------------------------
BEGIN;
INSERT INTO `d_firm` VALUES ('1', '车精灵1', '18626313098', '400888888', 'zhijian.yuan@rokyinfo.com', '无锡市滨湖区建筑西路599号国家工业设计园', 'http://www.rokyinfo.net', '2017-01-22 19:14:03'), ('2', '新日', '18626313098', '400888888', 'zhijian.yuan@rokyinfo.com', '无锡市滨湖区建筑西路599号国家工业设计园', 'http://www.rokyinfo.net', '2017-01-22 19:14:03');
COMMIT;

-- ----------------------------
--  Table structure for `d_mcu_device`
-- ----------------------------
DROP TABLE IF EXISTS `d_mcu_device`;
CREATE TABLE `d_mcu_device` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '设备ID',
  `udid` varchar(50) NOT NULL COMMENT '设备唯一标示',
  `model_id` bigint(20) NOT NULL COMMENT '型号ID',
  `hardware_version` varchar(10) DEFAULT NULL COMMENT '硬件版本号',
  `software_version` varchar(10) DEFAULT NULL COMMENT '软件版本号',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '设备状态 0：初始 1：生产 2：已出货 3：退货',
  `firm_id` bigint(20) NOT NULL COMMENT '厂商ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者ID',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `udid` (`udid`),
  KEY `model_id` (`model_id`),
  KEY `firm_id` (`firm_id`),
  CONSTRAINT `d_mcu_device_ibfk_1` FOREIGN KEY (`model_id`) REFERENCES `d_model` (`model_id`),
  CONSTRAINT `d_mcu_device_ibfk_2` FOREIGN KEY (`firm_id`) REFERENCES `d_firm` (`firm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='没有SN号的设备表';

-- ----------------------------
--  Table structure for `d_model`
-- ----------------------------
DROP TABLE IF EXISTS `d_model`;
CREATE TABLE `d_model` (
  `model_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `category` varchar(10) DEFAULT NULL COMMENT '分类 如：中控',
  `name` varchar(20) NOT NULL COMMENT '型号名称',
  `speciality` varchar(20) DEFAULT NULL COMMENT '特性（多个用逗号分隔, 如0,1 ）',
  `firm_id` bigint(20) DEFAULT NULL COMMENT '关联厂商ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`model_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='设备型号表';

-- ----------------------------
--  Table structure for `d_radio_info`
-- ----------------------------
DROP TABLE IF EXISTS `d_radio_info`;
CREATE TABLE `d_radio_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '通讯扩展信息ID',
  `sn` varchar(20) NOT NULL COMMENT '设备SN',
  `imei` char(15) NOT NULL COMMENT '国际移动设备身份码',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sn` (`sn`),
  CONSTRAINT `d_radio_info_ibfk_1` FOREIGN KEY (`sn`) REFERENCES `d_device` (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='RADIO信息表';

-- ----------------------------
--  Table structure for `sys_config`
-- ----------------------------
DROP TABLE IF EXISTS `sys_config`;
CREATE TABLE `sys_config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `key` varchar(50) DEFAULT NULL COMMENT 'key',
  `value` varchar(2000) DEFAULT NULL COMMENT 'value',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态   0：隐藏   1：显示',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统配置信息表';

-- ----------------------------
--  Table structure for `sys_log`
-- ----------------------------
DROP TABLE IF EXISTS `sys_log`;
CREATE TABLE `sys_log` (
  `id` bigint(18) NOT NULL COMMENT '主键',
  `title` varchar(512) DEFAULT NULL COMMENT '标题',
  `method` varchar(1024) DEFAULT NULL COMMENT '方法 - 执行的方法',
  `url` varchar(1024) DEFAULT NULL COMMENT '请求地址',
  `params` varchar(2048) DEFAULT NULL COMMENT '请求参数',
  `moduleType` int(9) DEFAULT NULL COMMENT '模块类型',
  `logLevel` int(1) DEFAULT NULL COMMENT '日志级别',
  `result` varchar(2048) DEFAULT NULL COMMENT '返回数据',
  `clientIp` varchar(32) DEFAULT NULL COMMENT '客户端IP',
  `errorMsg` varchar(2048) DEFAULT NULL COMMENT '错误信息',
  `creator` varchar(32) NOT NULL COMMENT '创建人',
  `createTime` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日志 - 记录系统日志';

-- ----------------------------
--  Table structure for `sys_menu`
-- ----------------------------
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu` (
  `menu_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) DEFAULT NULL COMMENT '父菜单ID，一级菜单为0',
  `name` varchar(50) DEFAULT NULL COMMENT '菜单名称',
  `url` varchar(200) DEFAULT NULL COMMENT '菜单URL',
  `perms` varchar(500) DEFAULT NULL COMMENT '授权(多个用逗号分隔，如：user:list,user:create)',
  `type` int(11) DEFAULT NULL COMMENT '类型   0：目录   1：菜单   2：按钮',
  `icon` varchar(50) DEFAULT NULL COMMENT '菜单图标',
  `order_num` int(11) DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='菜单管理';

-- ----------------------------
--  Table structure for `sys_role`
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
  `role_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(100) DEFAULT NULL COMMENT '角色名称',
  `remark` varchar(100) DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色';

-- ----------------------------
--  Table structure for `sys_role_menu`
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_menu`;
CREATE TABLE `sys_role_menu` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `role_id` bigint(20) DEFAULT NULL COMMENT '角色ID',
  `menu_id` bigint(20) DEFAULT NULL COMMENT '菜单ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色与菜单对应关系';

-- ----------------------------
--  Table structure for `sys_user`
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(100) NOT NULL COMMENT '密码',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `mobile` varchar(100) DEFAULT NULL COMMENT '手机号',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态  0：禁用   1：正常',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='系统用户';

-- ----------------------------
--  Records of `sys_user`
-- ----------------------------
BEGIN;
INSERT INTO `sys_user` VALUES ('1', 'admin', '8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918', 'zhijian.yuan@rokyinfo.com', '18626313098', '1', '2017-01-08 11:11:11');
COMMIT;

-- ----------------------------
--  Table structure for `sys_user_role`
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `role_id` bigint(20) DEFAULT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`user_id`),
  CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户与角色对应关系';

SET FOREIGN_KEY_CHECKS = 1;
